package org.pomirkovany.model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;

import org.pomirkovany.beans.Category;

public class CategoryPeer {
	public static Category getCategoryById(DataAccess dataAccess,
		      String categoryId) {
				Category category = null;
				Connection connection = dataAccess.getConnection();
				if (connection != null) {
		     
					ResultSet rs = null;	
				try {
		        Statement s = connection.createStatement();
		        String sql = "select category_id, category_name from"
		          + " categories where category_id=" + categoryId;
		        try {
		         rs = s.executeQuery(sql);
		          
		            if (rs.next()) {
		              category = new Category(rs.getInt(1), rs.getString(2));
		              }
		            
		           ; 
		          }
		        finally { s.close();
		        		rs.close();}
		        }
		      catch (SQLException e) {
		        System.out.println("Could not get categories: " + e.getMessage());
		        }
		      finally {
		        dataAccess.putConnection(connection);
		        }
		      }
				return category;
			  }
	
	public static int getCategoryId(DataAccess dataAccess,
		      String categoryName) {
				Category category = null;
				int result = -1;
				Connection connection = dataAccess.getConnection();
				if (connection != null) {
		      try {
		    	  ResultSet rs = null;
		        Statement s = connection.createStatement();
		        String sql = "select category_id from"
		          + " categories where category_name= '" + categoryName + "'";
		        try {
		          rs = s.executeQuery(sql);
		          
		            if (rs.next()) {
		              result = rs.getInt(1);
		              }
		            
		            
		          }
		        finally { s.close(); 
		        		rs.close();
		        }
		        }
		      catch (SQLException e) {
		        System.out.println("Could not get category: " + e.getMessage());
		        }
		      finally {
		        dataAccess.putConnection(connection);
		        }
		      }
				return result;
			  }
	
	public static void addCategory(DataAccess dataAccess,
		      String categoryName) {
				
				Connection connection = dataAccess.getConnection();
				if (connection != null) {
		      try {
		    	  ResultSet rs = null;
		        Statement s = connection.createStatement();
		        String sql = "insert into categories (category_name) "
		          + " VALUES( '" + categoryName + "')";
		        try {
		         s.executeUpdate(sql);
		          
		  
		            
		            
		          }
		        finally { s.close(); 
		        		if(rs!=null)
		        			rs.close();
		        }
		        }
		      catch (SQLException e) {
		        System.out.println("Could not add category: " + e.getMessage());
		        }
		      finally {
		        dataAccess.putConnection(connection);
		        }
		      }
				
			  }
			
			
			public static Hashtable getAllCategories(DataAccess dataManager) {
				Hashtable<String, String> categories = new Hashtable<String, String>();
				Connection connection = dataManager.getConnection();
				if (connection != null) {		
		      try {
		    	  
		    	ResultSet rs = null;
		        Statement s = connection.createStatement();
		        String sql = "select category_id, category_name from categories";
		        try {
		          rs = s.executeQuery(sql);
		          
		            while (rs.next()) {
		              categories.put(rs.getString(1), rs.getString(2));
		              }
		            
		          
		          }
		        finally {s.close(); 
		        rs.close();}
		        }
		      catch (SQLException e) {
		        System.out.println("Could not get categories: " + e.getMessage());
		        }
		      finally {
		        dataManager.putConnection(connection);
		        }
		      }
				return categories;
			  }
		  }
